# 使用提醒:
# 1. xbot包提供软件自动化、数据表格、Excel、日志、AI等功能
# 2. package包提供访问当前应用数据的功能，如获取元素、访问全局变量、获取资源文件等功能
# 3. 当此模块作为流程独立运行时执行main函数
# 4. 可视化流程中可以通过"调用模块"的指令使用此模块

import xbot
from xbot import print, sleep, web
from . import package
from .package import variables as glv
import re
import pymysql


def main(args):
    wb = web.create('http://www.boxofficecn.com/the-red-box-office', 'chrome')
    # 测试使用
    # wb = web.get_active('chrome', load_timeout=20)
    result = []
    movieCountryDict = {'🇨🇳': '中国', '🇺🇸': '美国', '🇮🇳': '印度', '🇯🇵': '日本', '🇫🇷': '法国', '🇬🇧': '英国',
                        '🇫🇮': '芬兰', '🇦🇺': '澳大利亚', '🇱🇧': '黎巴嫩'}
    lock = True
    while lock:
        we = wb.find_all_by_xpath('//tbody[@class="row-hover"]/tr')
        # 下页
        nextPageObj = wb.find_all_by_xpath('//*[@id="tablepress-4_next"]')[0]
        tabindex = nextPageObj.get_attribute('tabindex')

        for i in we:
            # 获取数据
            # 年份
            movieYear = i.find_all_by_xpath('./td[1]')[0].get_text()

            # 国家
            country = i.find_all_by_xpath('./td[1]/img')[0].get_attribute('alt')
            countryName = movieCountryDict[country]

            # 电影名称
            text = i.find_all_by_xpath('./td[2]')[0].get_text()
            movieName = ""
            match = re.match(r'^(.*?)\（', text)
            if match:
                movieName = match.group(1)
            else:
                movieName = ""

            # 评分
            movieScore = ""
            match = re.search(r'(\d+\.\d+)', text)
            if match:
                movieScore = match.group(1)
            else:
                movieScore = ""

            # 导演
            text = i.find_all_by_xpath('./td[3]')[0].get_text()
            movieDirector = re.sub(r'[^\w\u4e00-\u9fa5]', '', text)

            # 票房
            text = i.find_all_by_xpath('./td[4]')[0].get_text()
            movieBoxOffice = re.sub(r'\D', '', text)

            # 添加进列表
            result.append(
                [movieName, movieYear, countryName, movieScore, movieDirector, movieBoxOffice, 'Big watermelon'])

        if tabindex != '-1':
            nextPageObj.click()
        else:
            lock = False
    wb.close()

    # mysql连接
    connection = pymysql.connect(host='127.0.0.1',port=3306, user='root', password='123456', db='rpa')
    try:
        # 创建游标
        with connection.cursor() as cursor:
            # SQL语句
            sql = "INSERT INTO movie (movie_name,movie_year,movie_country,movie_score,movie_director,movie_box_office,submit_person) VALUES (%s, %s, %s, %s, %s, %s, %s)"
            # 构造参数列表
            values = result
            # 执行批量插入操作
            cursor.executemany(sql, values)
        # 提交事务
        connection.commit()
    finally:
        # 关闭连接
        connection.close()
    return 0



